package com.xinchuang.mapper.targetSystem;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xinchuang.entity.target.TargetBaseData;
import com.xinchuang.entity.targetSystem.system.SystemsRelationship;
import com.xinchuang.entity.targetSystem.system.Target;
import com.xinchuang.entity.targetSystem.system.TargetType;
import com.xinchuang.entity.targetSystem.target.TargetData;
import com.xinchuang.entity.targetSystem.target.TargetInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Set;

/**
 * @author zkwczx
 * @ClassName TargetMapper
 * @Description: 目标数据库操作类
 * @data 2021/4/2  11:01
 * @Version V1.0
 */
public interface TargetMapper extends BaseMapper<TargetType> {

    @Insert(value = {
            "INSERT INTO target_base_data ",
            "(target_id,target_name,target_classify,type,country,lon,lat,height,grade,index_value,risk,icon,sqlite_url,create_time,delete_sign) ",
            "VALUES ",
            "(#{targetId},#{targetName},#{targetClassify},#{type},#{country},#{lon},#{lat},#{height},#{grade},#{indexValue},#{risk},#{icon},#{sqliteUrl},CURRENT_TIMESTAMP,#{deleteSign})",
            "ON DUPLICATE KEY UPDATE target_name = #{targetName}, target_classify = #{targetClassify}, country = #{country}, lon = #{lon}, lat = #{lat}, height = #{height}, grade = #{grade}, index_value = #{indexValue}, risk = #{risk}, icon = #{icon}, sqlite_url = #{sqliteUrl}, delete_sign = #{deleteSign}"
    })
    int save(TargetData targetData);

    @Insert(value = {
            "INSERT INTO target_base_data ",
            "(target_id,target_name,target_classify,type,country,lon,lat,height,grade,index_value,risk,icon,sqlite_url,create_time,delete_sign) ",
            "VALUES ",
            "(#{targetId},#{targetName},#{targetClassify},#{type},#{country},#{lon},#{lat},#{height},#{grade},#{indexValue},#{risk},#{icon},#{sqliteUrl},'1949-01-01 00:00:00',#{deleteSign})",
    })
    int saveTime(TargetData targetData);

    @Select(value = {"SELECT target_classify FROM target_base_data WHERE target_id = #{targetId} LIMIT 1"})
    String findTaskByTargetClassify(@Param("targetId") String targetId);

    @Select(value = {
            "SELECT target_id,target_name,target_classify,type,country,icon,sqlite_url,lon,lat,height,delete_sign ",
            "FROM target_base_data WHERE target_id = #{targetId}"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify"),
            @Result(property = "type", column = "type"),
            @Result(property = "sqliteUrl", column = "sqlite_url"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "deleteSign", column = "delete_sign")
    })
    TargetData findTargetOnly(@Param("targetId") String targetId);

    @Select(value = {
            "SELECT target_id,target_name,target_classify,type,country,icon,sqlite_url,create_time,update_time,delete_sign ",
            "FROM target_base_data WHERE target_id = #{targetId} AND delete_sign = 1"
    })
    @Results(id = "targetSingleResultMap", value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify"),
            @Result(property = "type", column = "type"),
            @Result(property = "sqliteUrl", column = "sqlite_url"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "deleteSign", column = "delete_sign")
    })
    Target findById(@Param("targetId") String targetId);

    @Update(value = {
            "<script>",
            "UPDATE target_base_data ",
            "<set>",
            "<if test = 'targetName != null'>target_name = #{targetName},</if>",
            "<if test = 'targetClassify != null'>target_classify = #{targetClassify},</if>",
            "<if test = 'type != null'>type = #{type},</if>",
            "<if test = 'country != null'>country = #{country},</if>",
            "<if test = 'icon != null'>icon = #{icon},</if>",
            "<if test = 'sqliteUrl != null'>sqlite_url = #{sqliteUrl},</if>",
            "<if test = 'deleteSign != null'>delete_sign = #{deleteSign},</if>",
            "<if test = 'lon != null'>lon = #{lon},</if>",
            "<if test = 'lat != null'>lat = #{lat},</if>",
            "<if test = 'height != null'>height = #{height},</if>",
            "<if test = 'grade != null'>grade = #{grade},</if>",
            "<if test = 'indexValue != null'>index_value = #{indexValue},</if>",
            "<if test = 'risk != null'>risk = #{risk},</if>",
            "<if test = 'targetType != null'>target_type = #{targetType},</if>",
            "update_time = CURRENT_TIMESTAMP ,",
            "</set>",
            "WHERE target_id = #{targetId}",
            "</script>"
    })
    int update(TargetData targetData);

    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,target_classify,type,lon,lat,height,icon,sqlite_url FROM target_base_data ",
            "WHERE delete_sign = 1",
            "<if test = 'country != null'> ",
            "<foreach collection='country' item='item' index='index' open=' AND country IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'classify != null'> ",
            "<foreach collection='classify' item='item' index='index' open=' AND target_classify IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'keyword != null'> AND target_name LIKE '%${keyword}%'</if>",
            " ORDER BY update_time DESC",
            "</script>"
    })
    @Results(id = "targetAllMap", value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify"),
            @Result(property = "sqliteUrl", column = "sqlite_url")
    })
    List<TargetData> findAll(@Param("country") List<String> country, @Param("classify") List<String> classify, @Param("keyword") String keyword);

    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,target_classify,lon,lat,height,icon,sqlite_url FROM target_base_data ",
            "WHERE delete_sign = 1",
            "<if test = 'classify != null'> ",
            "AND target_classify LIKE '%${classify}%'",
            "</if>",
            "<if test = 'keyword != null'> AND target_name LIKE '%${keyword}%'</if>",
            " ORDER BY update_time DESC",
            "</script>"
    })
    @ResultMap(value = "targetAllMap")
    List<TargetData> findAlls(@Param("classify") String classify, @Param("keyword") String keyword);

    /**
     * 查询分类
     */
    @Select(value = {
            "SELECT type_id,parent_id,type_name,level FROM target_type WHERE level > 0 ORDER BY level ASC"
    })
    @Results(id = "typeMap", value = {
            @Result(property = "typeId", column = "type_id"),
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "typeName", column = "type_name"),
            @Result(property = "level", column = "level")
    })
    List<TargetType> selectTypeAll();

    /**
     * 根据等级查询类别
     */
    @Select(value = "SELECT type_id,parent_id,type_name,level FROM target_type WHERE level = #{level}")
    @ResultMap(value = "typeMap")
    List<TargetType> selectTypeByLevel(@Param("level") int level);

    /**
     * 查询子类分类ID
     */
    @Select(value = "SELECT type_id,parent_id,type_name,level FROM target_type WHERE parent_id = #{typeId}")
    @ResultMap(value = "typeMap")
    List<TargetType> selectSubType(@Param("typeId") String typeId);

    /**
     * 查询分类基础信息
     *
     * @param typeId
     * @return
     */
    @Select(value = "SELECT type_id,parent_id,type_name,level FROM target_type WHERE type_id = #{typeId} LIMIT 1")
    @ResultMap(value = "typeMap")
    TargetType selectType(@Param("typeId") String typeId);

    @Select(value = {
            "<script>",
            "SELECT type_id,parent_id,type_name,level FROM target_type WHERE type_id IN (",
            "<foreach collection='typeList' item='item' index='index' separator=','>",
            "#{item}",
            "</foreach>",
            ")",
            "</script>"
    })
    @ResultMap(value = "typeMap")
    List<TargetType> selectTypeList(@Param("typeList") List<String> typeList);

    /**
     * 查询子类分类ID
     */
    @Select(value = "SELECT type_id FROM target_type WHERE parent_id = #{typeId}")
    List<String> selectSubIdType(@Param("typeId") String typeId);

    /**
     * 查询分类级别
     */
    @Select(value = "SELECT level FROM target_type WHERE type_id = #{typeId}")
    Integer selectTypeLevel(@Param("typeId") String typeId);

    /**
     * 更新缩略图
     */
    @Update(value = {
            "UPDATE target_base_data SET ",
            "icon = #{icon},icon_url = #{iconUrl} ",
            "WHERE target_id = #{targetId}"
    })
    int updateTargetIcon(TargetData targetData);

    @Insert(value = {
            "<script>",
            "INSERT INTO target_info ",
            "(id,target_id,key,value,name,sign) ",
            "VALUES ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.id},#{item.targetId},#{item.key},#{item.value},#{item.name},#{item.sign})",
            "</foreach>",
            "</script>"
    })
    int insertTargetInfos(List<TargetInfo> list);

    @Delete(value = "DELETE FROM target_info WHERE target_id = #{targetId} AND sign = #{sign}")
    int deleteInfoByTargetId(@Param("targetId") String targetId, @Param("sign") int sign);

    /**
     * id列表查询相关信息
     *
     * @param targetList
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT a.target_id,a.target_name,a.lon,a.lat,a.height,b.type_name AS targetClassify,a.type AS targetType,a.index_value FROM ",
            "(SELECT target_id,target_name,lon,lat,height,icon,type,index_value FROM target_base_data ",
            "<foreach collection='list' item='item' index='index' open=' WHERE target_id IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            ") AS a ",
            "LEFT JOIN ",
            "(SELECT type_id,type_name FROM target_type) AS b ",
            "ON ",
            "a.type = b.type_id",
            "</script>",
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "indexValue", column = "index_value")
    })
    List<TargetData> getTargetList(List<String> targetList);

    /**
     * id列表查询相关信息
     *
     * @param targetList
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,lon,lat,height,icon,target_classify FROM target_base_data ",
            "<foreach collection='list' item='item' index='index' open=' WHERE target_id IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>",
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify")
    })
    List<TargetData> getTargets(List<String> targetList);

    /**
     * 获取体系目标关联ID
     * @param sysId
     * @return
     */
    @Select(value = {
            "SELECT tb.target_id,tb.target_name,tb.lon,tb.lat,tb.height,tb.icon,tb.type,sr.range_value FROM target_base_data tb JOIN systems_relation sr ON tb.target_id = sr.target_id WHERE sr.sys_id =  #{sysId}"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "rangeValue", column = "range_value")
    })
    List<TargetData> getSystemsTargets(String sysId);

    /**
     * id列表查询相关信息
     *
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT a.target_id,a.target_name,a.lon,a.lat,a.height,b.type_name AS targetClassify,a.target_classify AS targetType FROM ",
            "(SELECT target_id,target_name,lon,lat,height,target_classify FROM target_base_data WHERE delete_sign = 1 ",
            "<if test = 'keyword != null'> AND target_name LIKE '%${keyword}%' </if>",
            "<if test = 'country != null'>",
            "AND ",
            "<foreach collection='country' item='item' index='index' open='country IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "ORDER BY update_time DESC) AS a ",
            "LEFT JOIN ",
            "(SELECT type_id,type_name FROM target_type ",
            "<if test = 'type != null'> AND ",
            "<foreach collection='type' item='item' index='index' open=' WHERE type_name IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            ") AS b ",
            "ON ",
            "a.target_classify = b.type_id",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name")
    })
    List<TargetData> getTargetMap(@Param("keyword") String keyword, @Param("country") List<String> country, @Param("type") List<String> targetType);

    /**
     * 体系上图展示
     *
     * @param sysId
     * @return
     */
    @Select(value = {
            "SELECT sr.sys_ship_id,sr.sys_id,sr.target_one,sr.target_two,sr.point,sc.name AS relation_type,sc.color ",
            "FROM systems_relationship AS sr LEFT JOIN system_config AS sc ON sr.relation_type = sc.id ",
            "WHERE sr.sys_id = #{sysId}"
    })
    @Results(value = {
            @Result(property = "sysShipId", column = "sys_ship_id"),
            @Result(property = "sysId", column = "sys_id"),
            @Result(property = "targetOne", column = "target_one"),
            @Result(property = "targetTwo", column = "target_two"),
            @Result(property = "point", column = "point"),
            @Result(property = "relationType", column = "relation_type")
    })
    List<SystemsRelationship> getSystemsToMap(@Param("sysId") String sysId);

    @Select(value = {
            "SELECT sr.sys_ship_id,sr.sys_id,sr.target_one,sr.target_two,sr.point,sc.name AS relation_type,sc.color ",
            "FROM systems_relationship AS sr LEFT JOIN system_config AS sc ON sr.relation_type = sc.id ",
//            "WHERE sr.sys_id = #{sysId} AND (sr.target_one = #{targetId} OR sr.target_two = #{targetId})"
            "WHERE sr.sys_id = #{sysId} AND sr.target_one = #{targetId}"
    })
    @Results(value = {
            @Result(property = "sysShipId", column = "sys_ship_id"),
            @Result(property = "sysId", column = "sys_id"),
            @Result(property = "targetOne", column = "target_one"),
            @Result(property = "targetTwo", column = "target_two"),
            @Result(property = "point", column = "point"),
            @Result(property = "relationType", column = "relation_type")
    })
    List<SystemsRelationship> getSystemsTargetToMap(@Param("sysId") String sysId, @Param("targetId") String targetId);

    @Select(value = {
            "<script>",
            "SELECT sr.sys_ship_id,sr.sys_id,sr.target_one,sr.target_two,sr.point,sc.name AS relation_type,sc.color ",
            "FROM systems_relationship AS sr LEFT JOIN system_config AS sc ON sr.relation_type = sc.id ",
            "WHERE sr.sys_id = #{sysId} AND ( ",
            "<foreach collection='system' item='item' index='index' open='sr.target_one IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            " OR ",
            "<foreach collection='system' item='item' index='index' open='sr.target_two IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            ")",
            "</script>"
    })
    @Results(value = {
            @Result(property = "sysShipId", column = "sys_ship_id"),
            @Result(property = "sysId", column = "sys_id"),
            @Result(property = "targetOne", column = "target_one"),
            @Result(property = "targetTwo", column = "target_two"),
            @Result(property = "point", column = "point"),
            @Result(property = "relationType", column = "relation_type")
    })
    List<SystemsRelationship> getSystemsSystemToMap(@Param("sysId") String sysId, @Param("system") List<String> list);

    /**
     * 通过类别id查询名称
     *
     * @param typeId
     * @return
     */
    @Select(value = "SELECT type_name FROM target_type WHERE type_id = #{typeId}")
    String getTypeName(@Param("typeId") String typeId);

    /**
     * 查询不包含在系统的目标信息
     *
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,icon FROM target_base_data WHERE target_id NOT IN (SELECT target_id FROM system_relation)",
            "<if test = 'keyword != null'> AND target_name LIKE '%${keyword}%' OR country LIKE '%${keyword}%' </if>",
            "ORDER BY create_time DESC",
            "</script>"
    })
    @Results(
            value = {
                    @Result(property = "targetId", column = "target_id"),
                    @Result(property = "targetName", column = "target_name")
            }
    )
    List<TargetData> selectNotSystemTargets(@Param("keyword") String keyword);

    @Select(value = {
            "<script>",
            "SELECT type_id FROM target_type",
            "<foreach collection='list' item='item' index='index' open=' WHERE type_name IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    List<String> selectTargetTypeNames(@Param("list") List<String> list);

    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,target_classify,type,lon,lat,height FROM target_base_data",
            "<foreach collection='set' item='item' index='index' open=' WHERE target_id IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify")
    })
    List<TargetData> selectTargets(@Param("set") Set<String> list);

    /**
     * 删除目标关联信息
     */

    @Delete("DELETE FROM system_relation WHERE target_id = #{targetId}")
    int deleteSystemRelation(@Param("targetId") String targetId);

    @Delete("DELETE FROM systems_relation WHERE target_id = #{targetId}")
    int deleteSystemsRelation(@Param("targetId") String targetId);

    @Delete("DELETE FROM systems_relationship WHERE target_one = #{targetId} OR target_two = #{targetId}")
    int deleteSystemsShip(@Param("targetId") String targetId);

    @Select("SELECT target_id FROM target_base_data WHERE delete_sign = 1 AND target_name = #{name} LIMIT 1")
    String selectTargetName(@Param("name") String name);

    @Select("SELECT target_name FROM target_base_data WHERE delete_sign = 1 AND target_id = #{id}")
    String selectTargetNameById(@Param("id") String id);

    @Update(value = "UPDATE target_base_data SET index_value = #{indexValue} WHERE target_id = #{targetId}")
    int updateIndexValueByTargetId(String targetId, String indexValue);

}




